﻿using MongoDB.Bson;
using MongoDB.Driver;
using SpreadsheetGear;
using Sustainalytics.ClientDW.DataAccessLayer;
using Sustainalytics.ClientDW.Entities;
using Sustainalytics.ClientDW.Entities.Universes;
using Sustainalytics.DataLayer;
using Sustainalytics.DataLayer.Shared;
using Sustainalytics.Entities;
using Sustainalytics.Entities.ProductInvolvement;
using Sustainalytics.Entities.SustainableProducts;
using Sustainalytics.Portfolios.BL;
using Sustainalytics.ScreeningTool.BL.Model;
using Sustainalytics.Utils.Configuration;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Factory = SpreadsheetGear.Factory;

namespace Sustainalytics.ScreeningTool.BL
{
    /// <summary>
    /// this is done to keep  ExportScreening manageable
    ///  fits pregeneration needs
    /// </summary>
    public interface IExportScreeningAllAcccounts : IExportScreening
    {
        IWorkbook RunScreeningExportAllAccountsWorkbook(Guid filterId, Func<string> getEsgComputationUrl, string clientDw);
        byte[] RunScreeningExportAllAccounts(Guid filterId, Func<string> getEsgComputationUrl, string clientDw);
    }


    public class ExportScreeningEx : ExportScreening, IExportScreeningAllAcccounts
    {
        public ExportScreeningEx(ICompanyScreeningRepository companyScreeningRepository,
                                IClientDwRepository clientDWRepository,
                                IPortfolioReadHandler portfolioDetailsManager,
                                IUniversesRepository universeRepository,
                                IFilterCriteriaRepository filterCriteriaRepository,
                                IRepositoryCollection<CorporateData> repositoryCorporateData,
                                IScreeningDataRepository screeningDataRepository,
                                IMongoCollection<CorporateData> corporateDataCollection,
                                IMongoCollection<SustainableProductsFramework> sustainableProductsFramework,
                                IMongoCollection<ProductInvolvementFramework> productInvolvementFramework)
            : base(companyScreeningRepository, clientDWRepository, portfolioDetailsManager,
                    universeRepository, filterCriteriaRepository,
                    repositoryCorporateData, screeningDataRepository, 
                    corporateDataCollection, sustainableProductsFramework, productInvolvementFramework)
        {
            _companyScreening = CompanyScreening.GetInstance(
               companyScreeningRepository,
               clientDWRepository,
               portfolioDetailsManager,
               universeRepository,
               filterCriteriaRepository,
               null,
               screeningDataRepository,
               corporateDataCollection,
               sustainableProductsFramework,
               productInvolvementFramework,
               true);
        }


        public byte[] RunScreeningExportAllAccounts(Guid filterId, Func<string> getEsgUrlFromMatrixId, string clientDwCnxStr)
        {
            var screeningToolExportWorkBook = RunScreeningExportAllAccountsWorkbook(filterId, getEsgUrlFromMatrixId, clientDwCnxStr);
            var filterDetails = _companyScreeningRepository.GetFilterByFilterId(filterId);
            if (filterDetails.ExportType != null)
            {
                if (filterDetails.ExportType.Value == ExportType.Excel)
                    return screeningToolExportWorkBook.SaveToMemory(FileFormat.OpenXMLWorkbook);
                else
                {
                    screeningToolExportWorkBook.Worksheets[1].Name = "ScreeningResults";
                    return screeningToolExportWorkBook.Worksheets[1].SaveToMemory(FileFormat.CSV);
                }
            }
            return null;
        }

        public IWorkbook RunScreeningExportAllAccountsWorkbook(Guid filterId, Func<string> getEsgUrlFromMatrixId, string clientDwCnxstr)
        {
            ReportInformation = new ReportInformation() { HasScreeningData = false };

            var filterDetails = _companyScreeningRepository.GetFilterByFilterId(filterId);
            var filterCriterias = _companyScreeningRepository.GetFilterCriteriasByFilterId(filterId);
            var defaultEsgProfile = _companyScreeningRepository.GetDefaultProfile();
            var accountUniverseIds = _universeRepository.GetAllUniversesIds();

            var portfolioData = GetPortfolioDataFromFilter2(filterDetails, accountUniverseIds);

            var companiesById = (List<Guid>)null;
            if (filterDetails != null && filterDetails.PortfolioId != null)
                companiesById = _portfolioManager.GetCompanyIdsByPortfolio(filterDetails.PortfolioId.Value).ToList();

            var universes = (List<Universe>)null;
            if (!string.IsNullOrEmpty(filterDetails.UniverseName))
            {
                universes = _universeRepository.GetUniversesIdNameByName(filterDetails.UniverseName).ToList();
            }

            var accountId = filterDetails.AccountId;
            var accountName = _companyScreeningRepository.GetAccountNameForFtpReportByAccountId(filterDetails.AccountId);
            var claimsPrincipal = (new ClaimsReaderForISINCUSIPClaims(ConfigurationManager.AppSettings["RedisServer"], Convert.ToInt32(ConfigurationManager.AppSettings["DistributionDataSet"])))
                    .CollectAccountClaimsForISINAndCUSIP(String.Format("{0}#@%{1}", accountName, accountId));
            var helper = new CUSIPISINClaimsCheckerBL();
            helper.checkIsinCusipClaims(claimsPrincipal);


            var screeningReportData = new ScreeningReportData();
            screeningReportData.CompanyData = _screeningDataRepository.GetCompaniesDataAllAccounts(filterDetails,
                                                                        accountUniverseIds, // all universeids
                                                                        universes, // all universe
                                                                        portfolioData,
                                                                        filterCriterias,
                                                                        companiesById,
                                                                        helper.HasISINClaim,
                                                                        helper.HasCUSIPClaim);

            AddResultOutputToCompanyData(filterDetails, screeningReportData);

            GetCompanyDataFromComputation(filterDetails, filterCriterias, defaultEsgProfile, getEsgUrlFromMatrixId, clientDwCnxstr);

            screeningReportData.ClientName = Guid.Empty.ToString();
            screeningReportData.PortfolioName = filterDetails.PortfolioName;
            screeningReportData.FilterName = filterDetails.Name;
            screeningReportData.FilterSettings = filterDetails;

            return WriteToXLSAllAccounts(screeningReportData, filterDetails);
        }

        private List<PortfolioDetails> GetPortfolioDataFromFilter2(Filter filterDetails, List<int> accountUniverseIds)
        {
            var portfolioData = (List<PortfolioDetails>)null;
            var portfolioId = filterDetails.PortfolioId.GetValueOrDefault();
            if (portfolioId != Guid.Empty)
            {
                var securities = _portfolioManager.GetPortfolioSecurities(portfolioId, accountUniverseIds);
                portfolioData = securities.Select(x => x.GetPortfolioDetails()).ToList();
            }
            return portfolioData;
        }

        private IWorkbook WriteToXLSAllAccounts(ScreeningReportData screeningReportData, Filter filterDetails)
        {
            IWorkbookSet workbook = Factory.GetWorkbookSet();
            IWorkbook screeningToolExportWorkBook;

            var currentFilterCriterias = _companyScreeningRepository.GetFilterCriteriasByFilterId(filterDetails.Id);

            var templatePath = ConfigurationManager.AppSettings["templateFolder"];

            if (filterDetails.TargetType == TargetType.Screening)
            {
                templatePath = Path.Combine(templatePath, "Sample Screening Report" +
                                                        (filterDetails.PortfolioId.HasValue ? " - ClientId.xlsx" : ".xlsx"));
            }
            else
            {
                templatePath = Path.Combine(templatePath, "Sample Report Report" +
                                                        (filterDetails.PortfolioId.HasValue ? " - ClientId.xlsx" : ".xlsx"));
            }
            screeningToolExportWorkBook = workbook.Workbooks.Open(templatePath);
            var coverSheet = screeningToolExportWorkBook.Worksheets[0];
            var resultSheet = screeningToolExportWorkBook.Worksheets[1];

            bool isHeaderBuild = false;

            //// write results sheet
            /// get coverageEntity along with their research
            Dictionary<Guid, CorporateData> coverageEntitiesWithParentsDictionary = new Dictionary<Guid, CorporateData>();
            var allCoverageEntities = _repositoryCorporateData.ReadAllWhere(a => a.ResearchParentId != null);
            var coverageEntityParentIds = allCoverageEntities.Select(parent => parent.ResearchParentId.Value);
            var allResearchParentsWithCoverage = _repositoryCorporateData.ReadAllWhere(a => coverageEntityParentIds.Contains(a.Id));
            if (allCoverageEntities != null && allResearchParentsWithCoverage != null)
            {
                foreach (var coverageEntity in allCoverageEntities)
                {
                    if (!coverageEntity.ResearchParentId.HasValue)
                        continue;

                    var parent = allResearchParentsWithCoverage.FirstOrDefault(c => c.Id == coverageEntity.ResearchParentId.Value);
                    coverageEntitiesWithParentsDictionary.Add(coverageEntity.Id, parent);
                }
            }

            _dicCriteria = FullAccesDictionary(screeningReportData.CompanyData, currentFilterCriterias);
            _criterias = currentFilterCriterias;
            var rows = screeningReportData.CompanyData.Count;

            if (rows > 0)
            {
                var columns = _criterias.Count;
                var spread = SettingsHelper.Read("MaxDegreeOfParallelism", 1);
                var count = screeningReportData.CompanyData.Count;

                int perSheet = count / spread;
                var delta = count - perSheet * spread;
                perSheet += delta;
                var skip = 0;
                var list = new Tuple<IWorksheet, IEnumerable<BsonDocument>>[spread];
                var source = screeningReportData.CompanyData.OrderBy(p => p["CompanyNameLowerCase"].AsString);
                for (int i = 0; i < spread; i++)
                {
                    list[i] = new Tuple<IWorksheet, IEnumerable<BsonDocument>>(SpreadsheetGear.Factory.GetWorkbook().ActiveWorksheet, source.Skip(skip).Take(perSheet));
                    skip += perSheet;
                }

                var useRangeOptimizations = true;
                var localPC = GetProcessedColumn(filterDetails);
                Parallel.ForEach(list, tuple =>
                {
                    var headerIndex = 1;
                    var dict = new Dictionary<int, string>();
                    var subSheetRows = tuple.Item2.Count();

                    if (subSheetRows > 0)
                        tuple.Item1.Cells[1, localPC, subSheetRows, columns + localPC].NumberFormat = "@";
                    foreach (var output in tuple.Item2)
                    {
                        CorporateData parentEntityCorporateData = null;
                        if (coverageEntitiesWithParentsDictionary.ContainsKey(output["_id"].AsGuid))
                        {
                            parentEntityCorporateData = coverageEntitiesWithParentsDictionary[output["_id"].AsGuid];
                        }
                        WriteCompanyDataThread(filterDetails.Id, filterDetails, tuple.Item1, headerIndex, output, ref isHeaderBuild, dict, parentEntityCorporateData, useRangeOptimizations);
                        headerIndex++;
                    }
                    if (useRangeOptimizations)
                    {
                        SetupStyleForNumberData(tuple.Item1, tuple.Item2.Count(), dict);
                    }
                });

                var currentRow = 1;

                foreach (var cs in list)
                {
                    cs.Item1.UsedRange.Copy(resultSheet.Cells[currentRow, 0], PasteType.All, PasteOperation.None, true, false);
                    currentRow += cs.Item1.UsedRange.RowCount;
                }

                var headerIndexNew = 1;
                var processedColumn = localPC;
                foreach (FilterCriteria criteria in _criterias.OrderBy(p => p.Order))
                {
                    resultSheet.Cells[headerIndexNew - 1, processedColumn].Value = criteria.Name;
                    processedColumn++;
                }
                SetupCommonStyleData(resultSheet, rows, columns + localPC);

                // Refresh AutoFilter for the newly formed headers.
                resultSheet.Cells.AutoFilter();
                resultSheet.Cells.AutoFilter();
            }
            else
                throw new ApplicationException("No companies collected for current filter settings.");

            WriteCoverSheet(screeningReportData, coverSheet);

            if (filterDetails.TargetType == TargetType.Screening)
            {
                var filterSettingsSheet = screeningToolExportWorkBook.Worksheets[2];
                WriteFiltersSheet(filterSettingsSheet);
            }

            return screeningToolExportWorkBook;
        }

        private Dictionary<Guid, List<Guid>> FullAccesDictionary(List<BsonDocument> companyData, List<FilterCriteria> criteriaData)
        {
            var dict = new Dictionary<Guid, List<Guid>>();
            Parallel.ForEach(companyData, a =>
            {
                var list = new List<Guid>();
                foreach (var filterCriteria in criteriaData)
                {
                    list.Add(filterCriteria.Id);

                }
                lock (dict)
                {
                    dict.Add(a["_id"].AsGuid, list);
                }
            });
            return dict;
        }
    }
}


